﻿
CREATE PROC [admin].[MakeSecurityTrigger_Child]
	@TableName sysname,
	@TableParent sysname
AS
DECLARE @SQL nvarchar(max),@TableBase sysname,@TriggerName sysname,@ParentBase sysname
SET @TableBase = RIGHT(@TableName,LEN(@TableName)-CHARINDEX('_',@TableName))
--SET @ParentBase = RIGHT(@TableParent,LEN(@TableParent)-CHARINDEX('_',@TableParent))
SELECT @ParentBase = FKCOLUMN_NAME FROM fn_FKeys(@TableParent,@TableName)
SET @TriggerName = 'trs_'+@TableBase

IF OBJECT_ID(@TriggerName) > 0 SET @SQL = 'ALTER' ELSE SET @SQL = 'CREATE'
SET @SQL = @SQL + ' TRIGGER [dbo].['+@TriggerName+']
ON [dbo].['+@TableName+']
FOR INSERT,UPDATE,DELETE
AS

IF @@ROWCOUNT = 0 RETURN

--DECLARE @D datetime SET @D = GETDATE()

DECLARE @AccessID int,@RowCount int,@StatusID int,@TablePK int
CREATE TABLE #StatusIDs(StatusID int NOT NULL)
IF NOT EXISTS(SELECT * FROM inserted)BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM deleted d INNER JOIN '+@TableParent+' PT ON d.'+@ParentBase+' = PT.ID
	SELECT @RowCount = COUNT(*),@AccessID = 2 -- Update
	IF NOT EXISTS(SELECT * FROM #StatusIDs) RETURN
END
ELSE IF NOT EXISTS(SELECT * FROM deleted ) BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM inserted i INNER JOIN '+@TableParent+' PT ON i.'+@ParentBase+' = PT.ID
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END
ELSE BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM deleted d INNER JOIN '+@TableParent+' PT ON d.'+@ParentBase+' = PT.ID
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END

SELECT @StatusID = StatusID,@TablePK = PT.ID FROM (SELECT '+@ParentBase+' FROM inserted UNION SELECT '+@ParentBase+' FROM deleted)T
INNER JOIN '+@TableParent+' PT ON T.'+@ParentBase+' = PT.ID

/*T*/IF dbo.Config_Trace() = 1 BEGIN SELECT * INTO #inserted FROM inserted DECLARE @CU varbinary(999) SET @CU=COLUMNS_UPDATED()EXEC sp__TraceTrigger @@PROCID,@CU END/*T*/

DECLARE @CUs varbinary(1024) SET @CUs = COLUMNS_UPDATED()

EXEC sec__CheckTableAccess @RowCount,@CUs,@@PROCID,@StatusID,@AccessID,@TablePK

'
PRINT @SQL
EXEC (@SQL)
PRINT '--------------------------------------------------------------------------------------'

